package com.guinea.core.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;

import javax.sql.DataSource;

import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.database.search.TablesDependencyHelper;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.sqlloader.SqlLoaderControlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.ext.h2.H2Connection;
import org.dbunit.ext.oracle.OracleConnection;
import org.dbunit.operation.DatabaseOperation;

public class DbUtils {
	public static final String H2 = "h2";

	public static final String ORACLA = "oracle";

	String url;

	String username;

	String password;

	DataSource dataSource;

	Connection jdbcConnection;

	String schema;

	String dbtype;

	IDatabaseConnection databaseConnection;

	public DbUtils(String url, String username, String password, String schema,
			String dbtype) throws Exception {
		super();
		this.url = url;
		this.username = username;
		this.password = password;
		this.dbtype = dbtype;
		this.schema = schema == null ? "" : schema;
		if (H2.equalsIgnoreCase(dbtype)) {
			Class driverClass = Class.forName("org.h2.Driver");
			this.jdbcConnection = DriverManager.getConnection(url, username,
					password);
			this.databaseConnection = new H2Connection(this.jdbcConnection,
					schema);
		}
		if (ORACLA.equalsIgnoreCase(dbtype)) {
			Class driverClass = Class
					.forName("oracle.jdbc.driver.OracleDriver");
			this.jdbcConnection = DriverManager.getConnection(url, username,
					password);
			this.databaseConnection = new OracleConnection(this.jdbcConnection,
					schema);
		}
	}

	public void db2file() throws Exception {
	}

	public void file2db() {

	}

	public void db2db() {

	}

	public IDatabaseConnection getDatabaseConnection() {
		return databaseConnection;
	}

	public void setDatabaseConnection(IDatabaseConnection databaseConnection) {
		this.databaseConnection = databaseConnection;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public String getDbtype() {
		return dbtype;
	}

	public void setDbtype(String dbtype) {
		this.dbtype = dbtype;
	}

	public Connection getJdbcConnection() {
		return jdbcConnection;
	}

	public void setJdbcConnection(Connection jdbcConnection) {
		this.jdbcConnection = jdbcConnection;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getSchema() {
		return schema;
	}

	public void setSchema(String schema) {
		this.schema = schema;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public void db2fileExample(String filePath) throws Exception {
		// partial database export
		QueryDataSet partialDataSet = new QueryDataSet(databaseConnection);
		partialDataSet.addTable("FOO", "SELECT * FROM TABLE WHERE COL='VALUE'");
		partialDataSet.addTable("BAR");
		FlatXmlDataSet.write(partialDataSet,
				new FileOutputStream("partial.xml"));

		// full database export
		IDataSet fullDataSet = databaseConnection.createDataSet();
		FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));

		// dependent tables database export: export table X and all tables that
		// have a PK which is a FK on X, in the right order for insertion
		String[] depTableNames = TablesDependencyHelper.getAllDependentTables(
				databaseConnection, "X");
		IDataSet depDataset = databaseConnection.createDataSet(depTableNames);
		FlatXmlDataSet
				.write(depDataset, new FileOutputStream("dependents.xml"));
	}

	public static void main(String[] a) throws Exception {
		DbUtils d = new DbUtils("jdbc:h2:tcp://localhost/~/test", "sa", "", "",
				DbUtils.H2);

//		QueryDataSet partialDataSet = new QueryDataSet(d
//				.getDatabaseConnection());
//		String sql = "";
//		sql += " select NEXTVAL('SEQ_SK_DICTIONARY') id, 0 serial, 'root' parent_code, 'province' type_code, vprovcode code, vprovabb title, '1' status, 0 author, CURRENT_DATE() created, null modificator, null updated from tdn_province where vprovcode <> '00'";
//		sql += " union";
//		sql += " select NEXTVAL('SEQ_SK_DICTIONARY') id, 0 serial, vprovcode parent_code, 'city' type_code, vcitycode code, vcityname title, '1' status, 0 author, CURRENT_DATE() created, null modificator, null updated from tdn_city where vcitycode <> '00' and vcitycode <> '0'";
//		sql += " union";
//		sql += " select NEXTVAL('SEQ_SK_DICTIONARY') id, 0 serial, vcitycode parent_code, 'district' type_code, vdist_code code, vdistname title, '1' status, 0 author, CURRENT_DATE() created, null modificator, null updated from tdn_district where vdist_code <> '00'";
//		partialDataSet.addTable("SK_DICTIONARY", sql);
//		FlatXmlDataSet.write(partialDataSet, new FileOutputStream(
//				"doc/db-xml/cities.xml"));

		InputStream input = new FileInputStream(new File(DbUtils.class
				.getClassLoader().getResource(".").getPath()
				+ "../doc/db-xml/cities.xml"));
		IDataSet dataSet = new FlatXmlDataSetBuilder().setColumnSensing(true)
				.build(input);
		DatabaseOperation.INSERT.execute(d.getDatabaseConnection(), dataSet);

//		QueryDataSet partialDataSet = new QueryDataSet(d
//				.getDatabaseConnection());
//		partialDataSet.addTable("SK_DICTIONARY", "select * from SK_DICTIONARY");
//		FlatXmlDataSet.write(partialDataSet, new FileOutputStream(
//				"doc/db-xml/cities.xml"));

		System.out.println("ok");
	}
}
